package com.scalar.chiptrack.commons.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.commons.InternalComment;
import com.scalar.chiptrack.commons.forms.CreatePartNumberForm;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * User: Sujan
 * Date: Mar 21, 2007
 * Time: 12:39:48 PM
 */
public final class InternalCommentsDAO
{
    public static ArrayList getAllComments ( DBTransaction dbTransaction, String commentsGroupId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList internalComments = new ArrayList();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "SELECT ENTERED_BY_ID, COMMENT, CONVERT(VARCHAR,TIMESTAMP) AS TIMESTAMP  FROM INTERNAL_COMMENTS_TABLE WHERE GROUP_ID = ? ORDER BY CAST(TIMESTAMP AS DATETIME) DESC");
            pstmt.setString ( 1, commentsGroupId );

            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                internalComments.add ( new InternalComment( rset.getString("ENTERED_BY_ID"), rset.getString ( "COMMENT"), rset.getString ( "TIMESTAMP") ) );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load internal comments", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return internalComments;
    }
    public static void insertInternalComment ( DBTransaction dbTransaction, InternalComment internalComment ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "INSERT INTO INTERNAL_COMMENTS_TABLE(GROUP_ID, ENTERED_BY_ID, COMMENT, TIMESTAMP ) VALUES ( ?, ?, ?, CURRENT_TIMESTAMP )" );
            pstmt.setString ( 1, internalComment.getCommentGroupId() );
            pstmt.setString ( 2, internalComment.getEnteredById() );
            pstmt.setString ( 3, internalComment.getComment() );

            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch( Exception e )
        {
            throw new ScalarException ( "Could not update internal comment", e );
        }
        finally
        {
            if ( dbTransaction == null && !success && conn != null )
            {
                try { conn.rollback(); } catch( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static void updateCommentsGroupId(DBTransaction dbTransaction, String commentsgroupidauto, String newcommentsgroupidauto) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement("UPDATE [INTERNAL_COMMENTS_TABLE] SET [GROUP_ID] = ? WHERE [GROUP_ID] = ?");
            pstmt.setString(1, newcommentsgroupidauto);
            pstmt.setString(2, commentsgroupidauto);

            pstmt.executeUpdate();
            if (dbTransaction == null) {
                conn.commit();
            }
            success = true;
        }
        catch (Exception e) {
            throw new ScalarException("Could not update Internal Comments GroupId", e);
        }
        finally {
            if (dbTransaction == null && !success && conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ignore) {
                }
            }
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    public static String insertFgPartNumber ( DBTransaction dbTransaction, CreatePartNumberForm createPartNumberForm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        String  result = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( " { CALL SP_LOAD_FG_PARTS (?, ?, ?, ? ) } " );
            pstmt.setString ( 1, createPartNumberForm.getPartNumber() );
            pstmt.setString ( 2, createPartNumberForm.getDescription()  );
            pstmt.setString ( 3, createPartNumberForm.getPpackage()  );
            pstmt.setString ( 4, createPartNumberForm.getFamily()  );

            try
            {
                pstmt.executeUpdate();
            }
            catch( SQLException sqlEx )
            {
               result = sqlEx.getMessage();
            }

            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch( Exception e )
        {
            throw new ScalarException ( "Could not create new part number", e );
        }
        finally
        {
            if ( dbTransaction == null && !success && conn != null )
            {
                try { conn.rollback(); } catch( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return result;
    }
}
